<?php

function get_fund_array($empty_as_select_all = true) {
	elgg_load_library('elgg:oci_access');
	
	$result = array();
	if($empty_as_select_all) {
		$result['ALL'] = ' ';
	}
	$recordset = oci_query('select p_id, p_name from product_info'); 
	foreach($recordset as $record) {
		$result[$record[0]] = $record[1];
	}
	
	return $result;	
	
	
	/*
	//testing
	elgg_load_library('elgg:calendar');
	return array(
		'fund 1'=>last_working_day_of_last_week('2012-05-07'),
		'fund 2'=>last_working_day_of_last_year('2012-05-07'),
		'fund 3'=>'fund 3 text',
	);
	*/
}

function portfolio_get_overview($ref_date, $usr) {
	elgg_load_library('elgg:calendar');	
	elgg_load_library('elgg:misc');	
	$userID = mapping_usr_name_to_portfolio_access_id($usr);
	
	$nowDate = $ref_date;
	$yestDate = last_working_day($ref_date);
	$lastWeekDate = last_working_day_of_last_week($ref_date);
	$lastYearDate = last_working_day_of_last_year($ref_date);
	
	$sql_text = "";
	$sql_text .= "SELECT a.p_name,b.av,b.nav,100*(h.mv/b.av),100*(b.nav/c.nav-1),100*(b.nav/d.nav-1),100*(b.nav/e.nav-1) FROM ";
	$sql_text .= "(SELECT * FROM product_info WHERE p_id IN (SELECT p_id FROM product_view WHERE user_id= '$userID')) a LEFT JOIN ";
	$sql_text .= "(SELECT av,nav,p_id FROM nav_sc WHERE t_date=TO_DATE('$nowDate','yyyy-mm-dd')) b ON a.p_id=b.p_id LEFT JOIN ";
	$sql_text .= "(SELECT nav,p_id FROM nav_sc WHERE t_date=TO_DATE('$yestDate','yyyy-mm-dd')) c ON a.p_id=c.p_id LEFT JOIN ";
	$sql_text .= "(SELECT nav,p_id FROM nav_sc WHERE t_date=TO_DATE('$lastWeekDate','yyyy-mm-dd')) d ON a.p_id=d.p_id LEFT JOIN ";
	$sql_text .= "(SELECT nav,p_id FROM nav_sc WHERE t_date=TO_DATE('$lastYearDate','yyyy-mm-dd')) e ON a.p_id=e.p_id LEFT JOIN ";
	$sql_text .= "(SELECT SUM(f.num*g.p) AS mv,f.p_id AS p_id FROM ";
	$sql_text .= "(SELECT SUM(num) AS num,s_id,p_id FROM shares_chg WHERE t_date<=TO_DATE('$nowDate','yyyy-mm-dd') GROUP BY p_id,s_id HAVING SUM(num)<>0) f, ";
	$sql_text .= "(SELECT p,s_id FROM security_price WHERE t_date=TO_DATE('$nowDate','yyyy-mm-dd')) g ";
	$sql_text .= "WHERE f.s_id=g.s_id GROUP BY f.p_id) h ON a.p_id=h.p_id ORDER BY a.p_id ";
	
	$result = array();
	$recordset = oci_query($sql_text); 
	foreach($recordset as $record) {
		
		$tmp = array();  
		$tmp["Fund"]					=	$record[0];
		$tmp["Val"]			= $record[1];	   
		$tmp["NAV"]						=	$record[2];	         
		$tmp["% Wgt"]		= $record[3];        
		$tmp["% Daily Rtn"]	= $record[4];	
		$tmp["% WTD Rtn"]				= $record[5];	   
		$tmp["% YTD Rtn"]				= $record[6];	     

		array_push($result, $tmp);
	}
	
	return $result;
	
	//testing	
//	$tableArray[0]["Fund Name"]=count($recordset);
//	$tableArray[0]["Assert Value"]= count($recordset[0]);	
//	$tableArray[0]["NAV"]=$ref_date;	
//	$tableArray[0]["Weight (%)"]=$yestDate;
//	$tableArray[0]["Daily Return (%)"]=$lastWeekDate;	
//	$tableArray[0]["WTD (%)"]=$lastYearDate;	
//	$tableArray[0]["YTD (%)"]="2123131";	
//
//	$tableArray[1]["Fund Name"]="淡水泉成长一期";
//	$tableArray[1]["Assert Value"]="1.11";	
//	$tableArray[1]["NAV"]="NAV";	
//	$tableArray[1]["Weight (%)"]="234";
//	$tableArray[1]["Daily Return (%)"]="16.30";	
//	$tableArray[1]["WTD (%)"]="812123121";	
//	$tableArray[1]["YTD (%)"]="9321231231";	
//
//	$tableArray[2]["Fund Name"]="淡水泉成长一期";
//	$tableArray[2]["Assert Value"]="1.11";	
//	$tableArray[2]["NAV"]="NAV";	
//	$tableArray[2]["Weight (%)"]="451236";
//	$tableArray[2]["Daily Return (%)"]="2.88";	
//	$tableArray[2]["WTD (%)"]="1231123";	
//	$tableArray[2]["YTD (%)"]="231231221";	
//
//	return $tableArray;
}

function portfolio_get_holding($ref_date, $usr, $fund_id, $security_code_arr) {
	elgg_load_library('elgg:oci_access');
	elgg_load_library('elgg:calendar');	
	elgg_load_library('elgg:misc');	
	$userID = mapping_usr_name_to_portfolio_access_id($usr);
	
	$nowDate = $ref_date;
	$input_criteria = '';
	if($fund_id != 'ALL' && $fund_id != '') {
			$input_criteria .= "AND a.p_id='$fund_id' ";
	}
	if($security_code_arr && is_array($security_code_arr) && count($security_code_arr) > 0) {
			$input_criteria .= "AND a.s_id IN ('$security_code_arr[0]'";
			foreach($security_code_arr as $code) {
				$input_criteria .= ',';
				$input_criteria .= "'$code'";
			}
			$input_criteria .= ') ';
	}
	
	$sql_text = "";
	$sql_text .= "SELECT a.s_id,e.s_name,a.num,100*(a.num*a.c_p/d.av),100*(a.num*c.p/d.av),a.c_p,c.p,100*(SIGN(a.num)*(c.p/a.c_p-1)),d.av/(c.p*100),a.num*c.p AS mv,f.p_name FROM holding_cost a, ";
	$sql_text .= "(SELECT MAX(t_date) AS max_date,s_id,p_id FROM holding_cost WHERE t_date<=TO_DATE('$nowDate','yyyy-mm-dd') GROUP BY p_id,s_id) b, ";
	$sql_text .= "(SELECT s_id,p FROM security_price WHERE t_date=TO_DATE('$nowDate','yyyy-mm-dd')) c, ";
	$sql_text .= "(SELECT av,p_id FROM nav_sc WHERE t_date=TO_DATE('$nowDate','yyyy-mm-dd')) d, ";
	$sql_text .= "security_info e, ";
	$sql_text .= "product_info f ";
	$sql_text .= "WHERE a.t_date = b.max_date And a.s_id = b.s_id And a.p_id = b.p_id And a.num <> 0 And a.s_id = c.s_id And a.s_id = e.s_id And a.p_id = d.p_id And a.p_id = f.p_id ";
	$sql_text .= "AND a.p_id IN (SELECT p_id FROM product_view WHERE user_id= '$userID') ";
	$sql_text .= $input_criteria;
	$sql_text .= "ORDER BY a.p_id,mv DESC ";		
	
	$result = array();
	$recordset = oci_query($sql_text); 
	foreach($recordset as $record)
	{	
		$tmp = array();
		$tmp["Ticker"]		= $record[0];
		$tmp["Name"]	= $record[1];  
		$tmp["Pos"]	= $record[2];	         
		$tmp["% Mkt"]	= $record[4];	
		$tmp["% Cost"]	= $record[3];        
		$tmp["Avg Cost"]	= $record[5];	   
		$tmp["Mkt Price"]	= $record[6];	    
		$tmp["% Rtn"]=	$record[7];	   
		$tmp["% Num"]		= $record[8];	       
		//$tmp["Mkt Val"]		= $record[9];	     
		$tmp["Fund"]			= $record[10];
		
		array_push($result, $tmp);
	}     
	      
	return $result;
	

//	$tableArray[0]["Ticker"]="600432";
//	$tableArray[0]["Name"]="中航金机";	
//	$tableArray[0]["Quantity"]=count($recordset);	
//	$tableArray[0]["% of Cost"]="43.0";
//	$tableArray[0]["% of Mkt"]="6.30";	
//	$tableArray[0]["Avg. Cost"]="312";	
//	$tableArray[0]["Mkt Price"]="231";	
//	$tableArray[0]["Return (%)"]="231";	
//	$tableArray[0]["%1 Num"]=$fund_id;	
//	$tableArray[0]["Mkt Val"]=$input_criteria;	
//	$tableArray[0]["Fund"]=$sql_text;	
//
//	$tableArray[1]["Ticker"]="611212";
//	$tableArray[1]["Name"]="神州数码";	
//	$tableArray[1]["Quantity"]="21";	
//	$tableArray[1]["% of Cost"]="43.1";
//	$tableArray[1]["% of Mkt"]="6.31";	
//	$tableArray[1]["Avg. Cost"]="312";	
//	$tableArray[1]["Mkt Price"]="231";	
//	$tableArray[1]["Return (%)"]="231";	
//	$tableArray[1]["%1 Num"]="231";	
//	$tableArray[1]["Mkt Val"]="231";	
//	$tableArray[1]["Fund"]="淡水泉2淡水泉2淡水泉2";	
//
//	return $tableArray;
}

function portfolio_get_trade($start_date, $end_date, $usr, $fund_id, $security_code_arr) {
	elgg_load_library('elgg:oci_access');
	elgg_load_library('elgg:calendar');
	elgg_load_library('elgg:misc');	
	$userID = mapping_usr_name_to_portfolio_access_id($usr);
	
//	print_r($ref_date);
//	print_r($fund_id);
//	print_r($userID);
//	print_r($security_code_arr);

	$input_criteria = '';
	$input_criteria .= "AND t_date>=TO_DATE('$start_date','yyyy-mm-dd') AND t_date<=TO_DATE('$end_date','yyyy-mm-dd')";
		
	if($fund_id != 'ALL' && $fund_id != '') {
			$input_criteria .= "AND p_id='$fund_id' ";
	}
	if($security_code_arr && is_array($security_code_arr) && count($security_code_arr) > 0) {
			$input_criteria .= "AND s_id IN ('$security_code_arr[0]'";
			foreach($security_code_arr as $code) {
				$input_criteria .= ",'$code'";
			}
			$input_criteria .= ') ';
	}
	
	$sql_text = "";
	$sql_text .= "SELECT TO_CHAR(c.t_date, 'yyyy-mm-dd'),c.s_id,d.s_name,c.bs,c.num,c.p,d.currency,c.p_name, c.p_id FROM ";
	$sql_text .= "(SELECT a.*,b.p_name FROM  ";
	$sql_text .= "(SELECT * FROM trade WHERE p_id IN  ";
	$sql_text .= "(SELECT p_id FROM product_view WHERE user_id='$userID')  ";
	$sql_text .= $input_criteria;
	$sql_text .= ") a, product_info b WHERE a.p_id=b.p_id) c LEFT JOIN security_info d ON c.s_id=d.s_id ";
	$sql_text .= "ORDER BY c.t_date DESC,c.p_id,c.s_id ";
	
	//print_r($sql_text);
	
	$result = array();
	$reserved_portfolio_array = array();
	$recordset = oci_query($sql_text); 
	foreach($recordset as $record)
	{	
		$tmp = array();
		$tmp['Date']			= $record[0];
		$tmp['Ticker']		= $record[1];
		$tmp['Name']	= $record[2];  
		$tmp["B/S"]				= $record[3];	         
		$tmp["Quantity"]	= $record[4];        
		$tmp["Cost"]			= $record[5];	
		$tmp["Curncy"]	= $record[6];	   
		$tmp["Fund"]			= $record[7];	  
		//$tmp["Comments"]	= "个股波段";		
		array_push($result, $tmp);		
		array_push($reserved_portfolio_array, $record[8]);	
	} //foreach    
	
	append_trade_comments($result, $reserved_portfolio_array);
	return $result;	
}


function get_trade_comments($trade_list, $portfolio_id_list) 
{
	global $CONFIG;
	assert(count($trade_list)==count($portfolio_id_list));
	if(count($trade_list) == 0)
		return array();
	
	$tmp_insert_sql_text = 'insert into t_tmp_trades(trade_date,portfolio_id,sec_code) values ';
	
	$iter = 0;
	foreach($trade_list as $trade)
	{	
		//build insert sql...
		if($iter > 0) { //NOT first line
			$tmp_insert_sql_text .= ',';
		}
		$d = $trade['Date'];
		$p_id = $portfolio_id_list[$iter];
		$s_id = $trade['Ticker'];
		$tmp_insert_sql_text .= "('$d', '$p_id', '$s_id')";
		$iter++;
	}
	
	//create temporary table in Mysql
	elgg_query_runner("CREATE TEMPORARY TABLE t_tmp_trades (trade_date date NOT NULL,portfolio_id char(8) NOT NULL,sec_code char(6) NOT NULL)"); // TYPE = HEAP
	elgg_query_runner($tmp_insert_sql_text);
	elgg_query_runner('commit');
	
	$comments_q_sql = "select c.reason_tag as reason_tag, tmp.trade_date as d, tmp.portfolio_id as p, tmp.sec_code as s "; //
	$comments_q_sql .= "from daq_trade_comments c right join t_tmp_trades tmp ";
	$comments_q_sql .= "on c.trade_date=tmp.trade_date and c.portfolio_id=tmp.portfolio_id and c.sec_code=tmp.sec_code ";
	$comments_q_sql .= "ORDER BY tmp.trade_date DESC,tmp.portfolio_id,tmp.sec_code";
	
	$result = get_data($comments_q_sql);
	elgg_query_runner('DROP TABLE t_tmp_trades');
	return $result;
}

function append_trade_comments(&$trade_list, $portfolio_id_list) {
	if(count($trade_list) == 0)
		return;
		
	$comments = get_trade_comments($trade_list, $portfolio_id_list);
	assert(count($trade_list) == count($comments));
	
	$iter = 0;
	foreach($trade_list as $trade) 
	{		
		//check sequence...
		if(!($trade['Date']==$comments[$iter]->d 
			&& $trade['Ticker']==$comments[$iter]->s 			
			&& $portfolio_id_list[$iter]==$comments[$iter]->p)) {
				
				$msg = $comments[$iter]->d . $comments[$iter]->s . $comments[$iter]->p;
				throw new Exception('Mismatch in trade and comment.' . $msg);
		}
			
		$trade_list[$iter]["Comments"] = 
			build_comment_link($trade['Date'], $portfolio_id_list[$iter], $trade['Ticker'], 
											get_trade_reason($comments[$iter]->reason_tag));	//reference
		$iter++;	
	}
	
	return;
}

function check_comment_closed($trade_date)
{
	$today = strftime('%Y-%m-%d', time());
	$no_days = (strtotime($today) - strtotime($trade_date))/(60 * 60 * 24);
	//print_r($no_days);
	$is_comment_closed = ($no_days > 3);
	return $is_comment_closed;
}

function build_comment_link($trade_date, $portfolio_id, $sec_code, $msg) {
	//$today = strftime('%Y-%m-%d', time());
	//$comment_closed = ($today>$trade_date);
	$comment_closed = check_comment_closed($trade_date);
	
	$popup_url = elgg_get_site_url()."trade/comments?";	
	$popup_url .= 'date=' . urlencode($trade_date) . '&portfolio=' . urlencode($portfolio_id) . '&sec=' . urlencode($sec_code);
	
	if(!$msg) {
		$msg = ($comment_closed)? '...': '+';
	}
	$link = "<a href='JavaScript:newPopup(\"$popup_url\");' title='以新浏览器窗口方式打开'>$msg </a>";	
	return $link;
}

function portfolio_get_trade_review($trade_date, $portfolio_id, $security_code, &$summary) {
	global $CONFIG;
	
	$sql_text = "select reason_tag, reason_des, predict_horizon, blog_link from daq_trade_comments ";
	$sql_text .= "where trade_date='$trade_date' and portfolio_id='$portfolio_id' and sec_code='$security_code'";
	$result = get_data_row($sql_text);
	if($result) {
		$summary['Reason'] 				= $result->reason_tag;
		$summary['Remark'] 		 		= $result->reason_des;
		$summary['PredictHorizon'] = $result->predict_horizon;
		$blog_link = $result->blog_link;
		if($blog_link) {			
			$summary['BlogLink'] = explode(',', $blog_link);
		}
	} 
	
	return;
}

function portfolio_get_trade_summary($trade_date, $portfolio_id, $security_code, &$summary) {
	elgg_load_library('elgg:oci_access');
	
	$sql_text = "";
	$sql_text .= "SELECT b.bs,e.s_name,f.p_name,b.num,b.p,a.num AS Holding,a.c_p AS avgCost,c.p AS mktPrice,d.av AS assetValue FROM ";
	$sql_text .= "(SELECT num,c_p FROM holding_cost WHERE t_date=TO_DATE('$trade_date','yyyy-mm-dd') AND s_id='$security_code' AND p_id='$portfolio_id') a,  ";
	$sql_text .= "(SELECT bs,num,p FROM trade WHERE t_date=TO_DATE('$trade_date','yyyy-mm-dd') AND s_id='$security_code' AND p_id='$portfolio_id') b, ";
	$sql_text .= "(SELECT p FROM security_price WHERE t_date=TO_DATE('$trade_date','yyyy-mm-dd') AND s_id='$security_code') c, ";
	$sql_text .= "(SELECT av FROM nav_sc WHERE t_date=TO_DATE('$trade_date','yyyy-mm-dd') AND p_id='$portfolio_id') d, ";
	$sql_text .= "(SELECT s_name FROM security_info WHERE s_id='$security_code') e, ";
	$sql_text .= "(SELECT p_name FROM product_info WHERE p_id='$portfolio_id') f ";

	$recordset = oci_query($sql_text); 
	if(is_array($recordset) && count($recordset) > 0)
	{
		$summary['Date'] 				= $trade_date;
		$buy_sell 	= $summary["B/S"] 				= $recordset[0][0];
		$summary['Ticker'] 		= $security_code;
		$summary['Name'] 	= $recordset[0][1];
		$summary['FundID'] 			= $portfolio_id;
		$summary['Fund']				= $recordset[0][2];
		$exe_amt 		= $summary['ExeAmt']		= $recordset[0][3];
		$exe_price 	= $summary['ExePirce']	= $recordset[0][4];		
	}
	
	//calculate 
	$hld = $recordset[0][5];
	$avg_cost = $recordset[0][6];
	$mkt_price = $recordset[0][7];
	$asset_val = $recordset[0][8];
	
  if($buy_sell == "B") {
      $pre_hld = $hld - $exe_amt;
      if($pre_hld == 0) {
         $pre_avg_cost = 0;
      } else {
         $pre_avg_cost = ($hld*$avg_cost - $exe_amt*$exe_price*(1 + 0.001))/$pre_hld;
      }
      $pre_weight = $pre_hld*$mkt_price/$asset_val;
      $weight_chg = $exe_amt*$mkt_price/$asset_val;
      
  } else {
      $pre_hld = $hld + $exe_amt;
      $pre_avg_cost = $avg_cost;
      $pre_weight = $pre_hld*$mkt_price/$asset_val;
      $weight_chg = -$exe_amt*$mkt_price/$asset_val;    
  }	
  
  $summary['PreHld'] 			= $pre_hld;
  $summary['PreAvgCost']	= $pre_avg_cost;
  $summary['PreWeight'] 		= $pre_weight*100;  //change to percentage
  $summary['WeightChg'] 		= $weight_chg*100;  //change to percentage
  
  return;
}

function portfolio_get_trade_test($ref_date, $usr_id, $fund_id, $security_code_arr) {
	
	//TEST DATA...
	//"INSERT INTO `daq_trade_comments`(`trade_date`, `portfolio_id`, `sec_code`, `predict_horizon`, `reason_tag`, `reason_des`) VALUES ('2012-05-07','SC001','002311','M','不达预期','上市公司将发布一季报，业绩超预期')";
	
	global $CONFIG;
//	elgg_load_js('lightbox');
//	elgg_load_css('lightbox');
	
	//$sql_text = "select reason_tag from daq_trade_comments where trade_date='$ref_date' and portfolio_id='SC001' and sec_code='002311'";
	//$sql_text = "select comment_id from daq_trade_comments";
	//$recordset = get_data($sql_text);	
//	if(is_array($recordset) && count($recordset) > 0)	{
//	}

//	$record = get_data_row($sql_text);
//	if($record) {
//		$trade_reason_tag = $record->reason_tag;
//	}

	$tableArray[0]["Date"]="2012-4-30";
	$tableArray[0]["Ticker"]="600432";
	$tableArray[0]["Name"]="中航金机";	
	$tableArray[0]["B/S"]="B";	
	$tableArray[0]["Quantity"]="21";	
	$tableArray[0]["Price"]=12.3;
	$tableArray[0]["Currency"]='CNY';	
	$tableArray[0]["Fund"]="淡水泉1";	
	
	//$comments_url = elgg_get_site_url()."lightbox/comments/1";
	//$tableArray[0]["Comments"]="<a class='elgg-lightbox' href='$comments_url' title='以lightbox方式打开'>个股波段...</a>";	
	$popup_url = elgg_get_site_url()."trade/comments?";	
	$popup_url .= 'date=' . urlencode($ref_date) . '&fund=SC001' . '&sec=002311';
	$tableArray[0]["Comments"]="<a href='JavaScript:newPopup(\"$popup_url\");' title='以新浏览器窗口方式打开'>$trade_reason_tag...</a>";	
	

	$tableArray[1]["Date"]="2112-5-1";
	$tableArray[1]["Ticker"]="611212";
	$tableArray[1]["Name"]="神州数码";	
	$tableArray[1]["B/S"]="S";	
	$tableArray[1]["Quantity"]="21";	
	$tableArray[1]["Price"]="43.1";
	$tableArray[1]["Currency"]="CNY";	
	$tableArray[1]["Fund"]="淡水泉2";	
	
	//$comments_url = elgg_get_site_url()."lightbox/comments/2";
	//$tableArray[1]["Comments"]="<a class='elgg-lightbox' href='$comments_url' title='以lightbox方式打开'>个股波段...</a>";	
	$popup_url = elgg_get_site_url()."trade/comments?";
	$popup_url .= 'date=' . urlencode($ref_date) . '&fund=SC002' . '&sec=002311';
	$tableArray[1]["Comments"]="<a href='JavaScript:newPopup(\"$popup_url\");' title='以新浏览器窗口方式打开'>个股波段...</a>";	

	return $tableArray;
}


function portfolio_get_pnl($start_date, $end_date, $usr, $fund_id) {
	elgg_load_library('elgg:oci_access');
	elgg_load_library('elgg:calendar');
	elgg_load_library('elgg:misc');	
	$userID = mapping_usr_name_to_portfolio_access_id($usr);
	
	if(!is_working_day($start_date))
	{
		$start_date = last_working_day($start_date);
	}
	if(!is_working_day($end_date))
	{
		$end_date = last_working_day($end_date);
	}	
	if($start_date >= $end_date)
	{
		$error_info[0]["ERROR!"]="Input Error";
		$error_info[0]["Message"]="Start date should be earlier than end date.";	
		return $error_info;		
	}
	
	$Fund = $fund_id;

	$sql_text = "";
	$sql_text .= "SELECT f.s_id,g.s_name,f.amount,100*(f.amount/h.totalAV) FROM ";
	$sql_text .= "(SELECT e.s_id AS s_id,SUM(e.amount) AS amount FROM ";
	$sql_text .= "((SELECT a.s_id AS s_id,-a.sum_num*b.p AS amount FROM ";
	$sql_text .= "(SELECT SUM(num) AS sum_num,s_id FROM shares_chg WHERE t_date<=TO_DATE('$start_date','yyyy-mm-dd') AND p_id IN ('$Fund') AND p_id IN (SELECT p_id FROM product_view WHERE user_id= '$userID') GROUP BY s_id HAVING SUM(num)<>0) a,  ";
	$sql_text .= "(SELECT p,s_id FROM security_price WHERE t_date=TO_DATE('$start_date','yyyy-mm-dd')) b  ";
	$sql_text .= "WHERE a.s_id=b.s_id)  ";
	$sql_text .= "UNION ALL  ";
	$sql_text .= "(SELECT s_id,amount FROM cash_chg WHERE t_date>TO_DATE('$start_date','yyyy-mm-dd') AND t_date<=TO_DATE('$end_date','yyyy-mm-dd') AND p_id IN ('$Fund') AND p_id IN (SELECT p_id FROM product_view WHERE user_id= '$userID')) ";
	$sql_text .= "UNION ALL ";
	$sql_text .= "(SELECT c.s_id AS s_id,c.sum_num*d.p AS amount FROM  ";
	$sql_text .= "(SELECT SUM(num) AS sum_num,s_id FROM shares_chg WHERE t_date<=TO_DATE('$end_date','yyyy-mm-dd') AND p_id IN ('$Fund') AND p_id IN (SELECT p_id FROM product_view WHERE user_id= '$userID') GROUP BY s_id HAVING SUM(num)<>0) c, ";
	$sql_text .= "(SELECT p,s_id FROM security_price WHERE t_date=TO_DATE('$end_date','yyyy-mm-dd')) d ";
	$sql_text .= "WHERE c.s_id=d.s_id)) e GROUP BY e.s_id) f, ";
	$sql_text .= "security_info g, ";
	$sql_text .= "(SELECT SUM(av) AS totalAV FROM nav_sc WHERE t_date=TO_DATE('$start_date','yyyy-mm-dd') AND p_id IN ('$Fund') ";
	$sql_text .= "AND p_id IN (SELECT p_id FROM product_view WHERE user_id= '$userID')) h ";
	$sql_text .= "WHERE f.s_id=g.s_id ORDER BY f.amount DESC ";

	$result = array();
	$recordset = oci_query($sql_text); 
	foreach($recordset as $record)
	{	
		$tmp = array();
		$tmp["Ticker"]		= $record[0];
		$tmp["Name"]	= $record[1];  
		$tmp["PnL"]				= $record[2];	         
		$tmp["% Rtn"]		= $record[3];        
		
		array_push($result, $tmp);
	}     
	      
	return $result;


//	$tableArray[0]["Ticker"]="600432";
//	$tableArray[0]["Name"]=$sql_text;	
//	$tableArray[0]["PnL"]="123.12";	
//	$tableArray[0]["Return (%)"]="21.32";	
//
//	$tableArray[1]["Ticker"]="611212";
//	$tableArray[1]["Name"]="神州数码";	
//	$tableArray[1]["PnL"]="321.22";	
//	$tableArray[1]["Return (%)"]="53.44";	
//
//	return $tableArray;
}


function widget_get_top5holding() {
	elgg_load_library('elgg:oci_access');
	elgg_load_library('elgg:calendar');
	
	$ref_date = strftime('%Y-%m-%d', time());
	if(!is_working_day($ref_date))
	{
		$ref_date = last_working_day($ref_date);
	}
	
//	$owner = iconv('utf-8', 'gbk', '赵军');
	$owner_obj = elgg_get_page_owner_entity();
	$owner = $owner_obj->username;

	$sql_text = "";
	$sql_text .= "SELECT s_id,s_name FROM ";
	$sql_text .= "(SELECT a.s_id,c.s_name,a.sum_num*b.p AS mv FROM ";
	$sql_text .= "(SELECT s_id,SUM(num) AS sum_num FROM shares_chg WHERE t_date<=TO_DATE('$ref_date','yyyy-mm-dd') ";
	$sql_text .= "AND p_id IN (SELECT p_id FROM product_representative WHERE manager= '$owner') GROUP BY s_id HAVING SUM(num)<>0) a, ";
	$sql_text .= "(SELECT s_id,p FROM security_price WHERE t_date=TO_DATE('$ref_date','yyyy-mm-dd')) b, ";
	$sql_text .= "security_info c ";
	$sql_text .= "WHERE a.s_id=b.s_id AND a.s_id=c.s_id ";
	$sql_text .= "ORDER BY mv DESC)  ";
	$sql_text .= "WHERE rownum <=5 ";

	$result = array();
	$recordset = oci_query($sql_text); 
	foreach($recordset as $record)
	{	
		$tmp = array();
		$tmp["Ticker"]		= $record[0];
		$tmp["Name"]	= $record[1];     
		
		array_push($result, $tmp);
	}     
	      
	return $result;
	
	
/*	
	$tableArray[0]["Security"]=$sql_text;
	$tableArray[0]["Security Name"]=$owner;	
	//$tableArray[0]["Mkt Price"]="123";	

	$tableArray[1]["Security"]=count($recordset);
	$tableArray[1]["Security Name"]="神州数码";	
	//$tableArray[1]["Mkt Price"]="321";	

	return $tableArray;
*/

}

function widget_get_latest_trades() {
	elgg_load_library('elgg:oci_access');
	
	//$owner = iconv('utf-8', 'gbk', '赵军');
	$owner_obj = elgg_get_page_owner_entity();
	$owner = $owner_obj->username;

	$sql_text = "";
	$sql_text .= "SELECT TO_CHAR(a.t_date, 'yyyy-mm-dd'),a.s_id,b.s_name,a.bs FROM ";
	$sql_text .= "(SELECT DISTINCT t_date,s_id,bs FROM trade where p_id IN (SELECT p_id FROM product_representative WHERE manager= '$owner') ORDER BY t_date DESC) a,  ";
	$sql_text .= "security_info b  ";
	$sql_text .= "WHERE a.s_id=b.s_id AND rownum<=10 ";


	$result = array();
	$recordset = oci_query($sql_text); 
	foreach($recordset as $record)
	{	
		$tmp = array();
		$tmp["B/S"]				= $record[3];
		$tmp["Ticker"]		= $record[1];
		$tmp["Name"]	= $record[2];     
		$tmp["Date"]			= $record[0];
		//$tmp["Sec"] = $record[1] . " " . $record[2];
		
		array_push($result, $tmp);
	}     
	      
	return $result;
	
//	$tableArray[0]["B/S"]="B";
//	$tableArray[0]["Ticker"]="600432";
//	$tableArray[0]["Name"]=$owner;	
//	$tableArray[0]["Quantity"]="210";	
//
//	$tableArray[1]["B/S"]="S";
//	$tableArray[1]["Ticker"]="611212";
//	$tableArray[1]["Name"]="神州数码";	
//	$tableArray[1]["Quantity"]="120";	
//
//	return $tableArray;
}

function get_trade_reason_options_array($buy_sell) {
	if($buy_sell == 'B') {
		return array(
				'B1' => '个股精选',  																								 
				'B2' => '事件驱动',  																								 
				'B3' => '行业配置',  																								 
				'B4' => '大势判断',  																								 
				'B5' => '申赎调整',  																																				 
				);
	} else {
		return array(
				'S1' => '达到预期',
				'S2' => '风控要求',
				'S3' => '不达预期',
				'S4' => '策略终止',
				'S5' => '个股波段',
				'S6' => '申赎调整',
			);
	}
}

function get_trade_reason($key) {
	$options = array_merge(get_trade_reason_options_array('B'), 
											get_trade_reason_options_array('S'));
	
	return $options[$key];
}

function determine_trade_buy_sell($reason_tag) {
	$buy_options = get_trade_reason_options_array('B');
	if($buy_options[$reason_tag]) {
		return 'B';
	} else {
		return 'S';
	}
}

function get_trade_predict_horizon_options_array() {
	return array(
				'S' => '短线(<1M)',
				'M' => '中线(3M)',
				'L' => '长线(>6M)',
			);
}

function get_trade_predict_horizon($key) {
	$tmp = get_trade_predict_horizon_options_array();
	return $tmp[$key];
}